﻿using Dapper;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;

namespace SPDB.BTAP.Infrastructure.Data.DapperCore
{
    /// <summary>
    /// 数据库访问类
    /// </summary>
    public class DataBaseAccess
    {
        public static SqlConnection CreateConnection()
        {
            string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
            SqlConnection conn = new SqlConnection(connStr);
            return conn;
        }

        /// <summary>
        /// 执行增、删、改方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int Execute(string sql, object parms = null)
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Execute(sql, parms);
            }
        }

        /// <summary>
        /// 执行增、删、改方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Execute(sql
                    , param
                    , null
                    , null
                    , commandType);
            }
        }

        /// <summary>
        /// 得到单行单列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, object parms = null)
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.ExecuteScalar(sql, parms);
            }
        }

        public static IEnumerable<dynamic> Query(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query(sql, param, transaction, buffered, commandTimeout, commandType);
            }
        }
        public static IEnumerable<object> Query(Type type, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query(type, sql, param, transaction, buffered, commandTimeout, commandType);
            }
        }

        /// <summary>
        /// 单个数据集查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static List<TEntity> Query<TEntity>(string sql, Func<TEntity, bool> pre, object parms = null)
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TEntity>(sql, parms).Where(pre).Distinct().ToList();
            }
        }

        /// <summary>
        /// 单个数据集查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static List<TEntity> Query<TEntity>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TEntity>(sql, param, transaction, buffered, commandTimeout, commandType).Distinct().ToList();
            }
        }

        public static List<TReturn> Query<TReturn>(string sql, Type[] types, Func<object[], TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TReturn>(sql, types, map, param, transaction, buffered, splitOn, commandTimeout, commandType).Distinct().ToList();
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TFirst, TSecond, TReturn>(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType).Distinct().ToList();
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TFirst, TSecond, TThird, TReturn>(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType).Distinct().ToList();
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TThird, TFourth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType).Distinct().ToList();
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType).Distinct().ToList();
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType).Distinct().ToList();
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType).Distinct().ToList();
            }
        }

        /// <summary>
        /// 多个数据集查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static SqlMapper.GridReader MultyQuery(string sql, object parms = null)
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.QueryMultiple(sql, parms);
            }
        }

        /// <summary>
        /// 单个数据集查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static TEntity FirstOrDefault<TEntity>(string sql, Func<TEntity, bool> selector, object parms = null)
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.Query<TEntity>(sql, parms).Where(selector).FirstOrDefault();
            }
        }

        public static TEntity QueryFirst<TEntity>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.QueryFirst<TEntity>(sql, param, transaction, commandTimeout, commandType);
            }
        }

        public static TEntity QueryFirstOrDefault<TEntity>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (IDbConnection conn = CreateConnection())
            {
                return conn.QueryFirstOrDefault<TEntity>(sql, param, transaction, commandTimeout, commandType);
            }
        }
        /// <summary>
        /// 单个数据集查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataTable QueryTable(string sql)
        {
            using (IDbConnection conn = CreateConnection())
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn as SqlConnection);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                DataTable dt = ds.Tables[0];

                return dt;
            }
        }

        public static int ExecSql(string sql)
        {
            int i = 0;

            using (IDbConnection conn = CreateConnection())
            {
                i = conn.Execute(sql);
            }

            return i;
        }

    }
}
